package cn.sfa.common.quartzJob;

import java.sql.CallableStatement;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hsqldb.Types;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import cn.sfa.common.quartzJob.AccountJob.AccountEntity;
import cn.sfa.entity.quartz.SaleQuartzMessage;
import cn.sfa.entity.smsmessage.SaleSmsMessage;

public class QuartzMessageService {

	private NamedParameterJdbcTemplate jdbcTemplate;

	public NamedParameterJdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(NamedParameterJdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
 

	/*
	 * 获取待发送的消息
	 */
	public List<SaleQuartzMessage> queryUnSend(){
		
		//先用批次号锁定消息
		String sql = "BEGIN pkg_sfa_SALEMESSAGRSSTAKE.lockSendMessage(:P_BatchCode); END;";
		Map<String, Object> paramMap = new HashMap<String, Object>(); 
		String P_BatchCode = "";
		paramMap.put("P_BatchCode", P_BatchCode);  
		P_BatchCode = jdbcTemplate.getJdbcOperations().execute(sql,
				new CallableStatementCallback<String>() {
					@Override
					public String doInCallableStatement(CallableStatement cs)
							throws SQLException, DataAccessException {  
						cs.registerOutParameter( 1 , Types.VARCHAR);
						cs.execute();
						return cs.getString(1);
					}
				});

		//根据批次号获取消息
		String sql2 = "select * from table(pkg_sfa_SALEMESSAGRSSTAKE.getUnSendMessage(:P_BatchCode)) ";
		Map<String, Object> parammap = new HashMap<String, Object>();
		parammap.put("P_BatchCode", P_BatchCode); 

		List<SaleQuartzMessage> result = jdbcTemplate.query(sql2, parammap,
				new BeanPropertyRowMapper<SaleQuartzMessage>(
						SaleQuartzMessage.class));
		return result; 
	}
	
	/**
	 * 发送成功的操作
	 * @param messageId
	 */
	public void setSended(String messageId) {
		String sqlStr = " update SALEMESSAGRSSTAKE set SENDSTATUS = 'SENDSTATUS_03' where salemessagrsstakeid=:salemessagrsstakeid ";

		Map<String, Object> paramMap = new HashMap<String, Object>();
		
		paramMap.put("salemessagrsstakeid", messageId);
		jdbcTemplate.update(sqlStr, paramMap); 
	}
	
	
	/**
	 * 获取短信数目
	 * @return
	 */
	public List<SMSmessageEntity> getSmsMessage(){
		StringBuffer sql = new StringBuffer();
		sql.append(" select distinct a.smscontext, a.recvmobileno ");
		sql.append(" from smsmessage a ");
		sql.append(" where a.sendstatus = 'SMSSENDSTATUS_2' and  sysdate < a.validdatetime  "); 
		sql.append(" minus ");
		sql.append(" select c.smscontext, c.recvmobileno ");
		sql.append(" from smsmessage c ");
		sql.append(" where c.sendstatus = 'SMSSENDSTATUS_3' ");
		sql.append(" and exists (select * ");
		sql.append(" from smsmessage b ");
		sql.append(" where b.recvmobileno=c.recvmobileno ");
		sql.append(" and b.smscontext=c.smscontext  ");
		sql.append(" and b.sendstatus = 'SMSSENDSTATUS_2' ");
		sql.append(" and c.senddate>b.createdate - interval '10' hour ");
		sql.append(" and rownum <= 1 ) ");
		Map<String, Object> paramMap = new HashMap<String, Object>();
		return jdbcTemplate.query(sql.toString(), paramMap,
				new BeanPropertyRowMapper<SMSmessageEntity>(SMSmessageEntity.class));
	} 
	/**
	 * 发送失败的操作
	 * @param messageId
	 */
	public void setSMSFail(String recvmobileno,String smscontext) {
		String sqlStr = " update smsmessage set SENDSTATUS = 'SMSSENDSTATUS_4' where recvmobileno=:recvmobileno and smscontext=:smscontext";
		Map<String, Object> paramMap = new HashMap<String, Object>();		
		paramMap.put("recvmobileno", recvmobileno);
		paramMap.put("smscontext", smscontext);
		jdbcTemplate.update(sqlStr, paramMap); 
	}
	/**
	 * 发送成功的操作
	 * @param messageId
	 */
	public void setSMSSend(String recvmobileno,String smscontext) {
		String sqlStr = " update smsmessage set SENDSTATUS = 'SMSSENDSTATUS_3',senddate =sysdate  where recvmobileno=:recvmobileno and smscontext=:smscontext ";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("recvmobileno", recvmobileno);
		paramMap.put("smscontext", smscontext);
		jdbcTemplate.update(sqlStr, paramMap); 
	}
}
